package cn.jekun.shop.common.service;

import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;

import cn.jekun.component.base.bean.Page;
import cn.jekun.component.base.exceptions.BusinessException;
import cn.jekun.component.base.utils.SqlUtis;
import cn.jekun.component.base.validator.annotation.Security;
import cn.jekun.component.core.Constants;
import cn.jekun.component.core.bean.gen.DCity;
import cn.jekun.component.core.bean.gen.DRegion;
import cn.jekun.component.core.bean.gen.ErpInventory;
import cn.jekun.component.core.bean.gen.ErpMerchandise;
import cn.jekun.component.core.bean.gen.ErpMerchandiseImage;
import cn.jekun.component.core.bean.gen.ErpMerchandiseType;
import cn.jekun.component.core.bean.gen.ErpProductHis;
import cn.jekun.component.core.bean.gen.ErpShop;
import cn.jekun.component.core.bean.gen.ErpStat;
import cn.jekun.component.core.service.gen.ErpInventoryService;
import cn.jekun.component.core.service.gen.ErpMerchandiseImageService;
import cn.jekun.component.core.service.gen.ErpProductHisService;
import cn.jekun.component.core.service.gen.ErpStatService;
import cn.jekun.component.shop.bean.Goods;
import cn.jekun.shop.web.bean.ErpStats;

@Service(GoodsService.BEAN_NAME)
public class GoodsService extends ShopBaseService{
	public static final String BEAN_NAME = "cn.jekun.shop.common.service.GoodsService";
	@Override
	public String getNamespace() {
		return "SHOP_GOODSMESSAGE";
	}
	@Resource(name=ErpProductHisService.BEAN_NAME)
	ErpProductHisService erpProductHisService;
	@Resource(name=ErpStatService.BEAN_NAME)
	ErpStatService erpStatService;
	@Resource(name=ErpInventoryService.BEAN_NAME)
	ErpInventoryService inventoryService;
	@Resource(name=ErpMerchandiseImageService.BEAN_NAME)
	ErpMerchandiseImageService erpMerchandiseImageService;
	
	/**
	 * 商品查询
	 * 
	 * @param pageSize
	 *            页大小
	 * @param currentPage
	 *            当前页
	 * @param typeId
	 *            商品类型编码
	 * @param cityId
	 *            已开通城市代码
	 * @param regionId
	 *            地区代码
	 * @param distance
	 *            距离
	 * @param lng
	 *            经度
	 * @param lat
	 *            纬度
	 * @param shopId
	 *            商户编码
	 * @param sort
	 *            排序字段
	 * @param barCode
	 *            条形码
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public JSONObject queryGoods(
			@Security
			String pageSize, 
			@Security
			String currentPage,
			@Security
			String goodType, 
			@Security
			String shopType, 
			@Security
			@DCity.AnnoCityId
			String cityId, 
			@Security
			@DRegion.AnnoRegionId
			String regionId,
			@Security
			String distance, 
			@Security
			String lng, 
			@Security
			String lat, 
			@Security
			String shopId, 
			@Security
			String sort,
			@Security
			String goodName,
			@Security
			String barCode,
			@Security
			String sessionId,
			@Security
			String userId
			) {
		if(goodType==null){//20160519 ios 报空值异常
			goodType = "";
		}
		if (StringUtils.isBlank(pageSize)) {
			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
					"页大小不能为空");
		}
		if (StringUtils.isBlank(currentPage)) {
			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
					"当前页不能为空");
		}
//		if (StringUtils.isBlank(cityId)) {
//			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
//					"城市编码不能为空");
//		}
		if (StringUtils.isBlank(sort)) {
			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
					"排序字段不能为空");
		}
		if(StringUtils.isNotBlank(shopId)){
			cityId = "";
		}
		if (Constants.CITY_ALL.equals(cityId)) {//全国
			cityId = null;
		}
		JSONObject jsonObject = new JSONObject();
		Page page = new Page();
		List resultList = new ArrayList();
		Map formMap = new HashMap();
		page.setPageSize(Integer.parseInt(pageSize));
		page.setPageNumber(Integer.parseInt(currentPage));
		String order = "";
		if(!StringUtils.isBlank(userId)){
			formMap.put("userId", userId);
		}else{
			formMap.put("sessionId", sessionId);
		}
		//formMap.put(ErpMerchandise.COL_SHOP_MERCHANDISE_TYPE_ID, goodType);
		formMap.put(ErpShop.COL_SHOP_TYPE_ID, shopType);
		formMap.put(ErpShop.COL_CITY_ID, cityId);
		formMap.put(DRegion.COL_REGION_ID, regionId);
		formMap.put("distance", distance);
		//lng="113.224398";
		//lat="23.400074";
		formMap.put("lng", lng);
		formMap.put("lat", lat);
		formMap.put(ErpShop.COL_SHOP_ID, shopId);
//		if(StringUtils.isBlank(shopId)){
//			formMap.put(ErpShop.COL_BUSINESS_STATUS,Constants.SHOP_STATUS_ON_BUSSINESS);
//		}
		formMap.put(ErpMerchandise.COL_MERCHANDISE_NAME, goodName);
		formMap.put(ErpMerchandise.COL_BARCODE, barCode);
		formMap.put(ErpMerchandise.COL_CLOSED, Constants.GOODS_OPEN);//上架商品
		formMap.put(ErpMerchandise.COL_VIRTUAL, Constants.GOODS_UNVIRTUAL);//普通商品
		// 0:默认(不排序) 1:销量(sales)高到低 2：价格(price)高到低 3：价格（price）低到高 4：距离(distance)近到远
		switch (Integer.parseInt(sort))

		{
		
		case 0: 
			order = " case when a.recommended is null then 0 else to_number(a.recommended) end desc nulls last , case when a.seo_weight is null then 0 else a.seo_weight end desc nulls last , case when a.update_time is null then a.create_time else a.update_time end desc nulls last";
			break;
		case 1:
			order = "  sales desc nulls last ";
			break;
		case 2:
			order = " price desc nulls last ";
			break;
		case 3:
			order = " price asc ";
			break;
		case 4:
			if(StringUtils.isBlank(lng) && StringUtils.isBlank(lat)){
				order = " ";
			}else{
				order = " distance asc ";
			}
			break;
		}
		// 当经纬度的值为空或者为""采用普通查询
		if (StringUtils.isBlank(lng) && StringUtils.isBlank(lat)) {
			
			String fieldStr="";
			if(!StringUtils.isBlank(shopId)){
				
				if(goodType.indexOf("/")>=0){
					goodType=goodType.replace("/", ",");
					fieldStr=StringUtils.isBlank(goodType)?"":"and a.SHOP_MERCHANDISE_TYPE_ID in (select MERCHANDISE_TYPE_ID from erp_merchandise_type where MERCHANDISE_TYPE_ID in ("+goodType+") or parent_id in("+goodType+")  and SHOP_ID='"+shopId+"')";

				}else{
					
					fieldStr=StringUtils.isBlank(goodType)?"":"and a.SHOP_MERCHANDISE_TYPE_ID in (select MERCHANDISE_TYPE_ID from erp_merchandise_type where MERCHANDISE_TYPE_ID = '"+goodType+"' or parent_id='"+goodType+"'  and SHOP_ID='"+shopId+"')";

				}
				
			}else{
				if(goodType.indexOf("/")>=0){
					goodType=goodType.replace("/", ",");
					fieldStr=StringUtils.isBlank(goodType)?"":"and a.MERCHANDISE_TYPE_ID in (select MERCHANDISE_TYPE_ID from erp_merchandise_type where MERCHANDISE_TYPE_ID in( "+goodType+") or parent_id in("+goodType+"))";

				}else{
					
					fieldStr=StringUtils.isBlank(goodType)?"":"and a.MERCHANDISE_TYPE_ID in (select MERCHANDISE_TYPE_ID from erp_merchandise_type where MERCHANDISE_TYPE_ID = '"+goodType+"' or parent_id='"+goodType+"')";

				}

			}
			
			
			SqlUtis.prepareSql(formMap, 
					fieldStr
					//SqlUtis.getSQL(formMap,"a."+ ErpMerchandise.COL_SHOP_MERCHANDISE_TYPE_ID,Types.VARCHAR, SqlUtis.EQ) 
					,SqlUtis.getSQL(formMap, "c."
					+ ErpShop.COL_REGION_ID, Types.VARCHAR, SqlUtis.EQ),
					SqlUtis.getSQL(formMap, "c." + ErpShop.COL_CITY_ID,
							Types.VARCHAR, SqlUtis.EQ), SqlUtis.getSQL(formMap,
							"c." + ErpShop.COL_SHOP_ID, Types.VARCHAR,
							SqlUtis.EQ), SqlUtis.getSQL(formMap, "c."
							+ ErpShop.COL_SHOP_TYPE_ID, Types.VARCHAR,
							SqlUtis.EQ),
							SqlUtis.getSQL(formMap, "a."+ErpMerchandise.COL_MERCHANDISE_NAME, Types.VARCHAR, SqlUtis.LIKE),
							SqlUtis.getSQL(formMap, "a."+ErpMerchandise.COL_BARCODE, Types.VARCHAR, SqlUtis.EQ),
							SqlUtis.getSQL(formMap, "a."+ErpMerchandise.COL_CLOSED, Types.VARCHAR, SqlUtis.EQ),
							SqlUtis.getSQL(formMap, "a."+ErpMerchandise.COL_VIRTUAL, Types.VARCHAR, SqlUtis.EQ),
							SqlUtis.getSQL(formMap, "c."+ErpShop.COL_BUSINESS_STATUS,Types.VARCHAR,SqlUtis.EQ),
							" and c.BUSINESS_STATUS <> '4'",//结业
			StringUtils.isNotBlank(order)?SqlUtis.orderBy(order):"" );
			resultList = this.list(formMap, page);
		} else {
			order = "case when t1.recommended is null then 0 else to_number(t1.recommended) end desc nulls last , case when t1.merchandiseWeight is null then 0 else t1.merchandiseWeight end desc nulls last , case when t1.update_time is null then t1.create_time else t1.update_time end desc nulls last";
			SqlUtis.prepareSql(formMap,
					//SqlUtis.getSQL(formMap, "d."+ ErpMerchandiseType.COL_MERCHANDISE_TYPE_ID,Types.VARCHAR, SqlUtis.EQ)
					//StringUtils.isBlank(goodType)?"":"and d.MERCHANDISE_TYPE_ID in (select MERCHANDISE_TYPE_ID from erp_merchandise_type where MERCHANDISE_TYPE_ID = '"+goodType+"' or parent_id='"+goodType+"')"
							SqlUtis.getSQL(formMap,"t1."+ ErpMerchandise.COL_SHOP_MERCHANDISE_TYPE_ID,Types.VARCHAR, SqlUtis.EQ), 
							SqlUtis.getSQL(formMap, "t2."+ ErpShop.COL_REGION_ID, Types.VARCHAR, SqlUtis.EQ),
							SqlUtis.getSQL(formMap, "t2." + ErpShop.COL_CITY_ID,Types.VARCHAR, SqlUtis.EQ), 
							SqlUtis.getSQL(formMap,"t1." + ErpShop.COL_SHOP_ID, Types.VARCHAR,SqlUtis.EQ),
//							SqlUtis.getSQL(formMap, "c." + ErpShop.COL_LONGITUDE,Types.VARCHAR, SqlUtis.EQ), 
//							SqlUtis.getSQL(formMap,"c." + ErpShop.COL_LATITUDE, Types.VARCHAR,SqlUtis.EQ), 
							SqlUtis.getSQL(formMap, "t2."+ ErpShop.COL_SHOP_TYPE_ID, Types.VARCHAR,SqlUtis.EQ), 
//							SqlUtis.getSQL(formMap, "q."+ "distance", Types.VARCHAR, SqlUtis.LE),
							SqlUtis.getSQL(formMap, "t1."+ErpMerchandise.COL_MERCHANDISE_NAME, Types.VARCHAR, SqlUtis.LIKE),
							SqlUtis.getSQL(formMap, "t1."+ErpMerchandise.COL_BARCODE, Types.VARCHAR, SqlUtis.EQ),
//							SqlUtis.getSQL(formMap, "a."+ErpMerchandise.COL_CLOSED, Types.VARCHAR, SqlUtis.LIKE),
//							SqlUtis.getSQL(formMap, "a."+ErpMerchandise.COL_VIRTUAL, Types.VARCHAR, SqlUtis.EQ),
							//" and c.BUSINESS_STATUS <> '4'",//结业
//							" and c.BUSINESS_STATUS <> '4'  AND NVL2 (DECODE (SIGN (c.range - q.distance), 1, '1', 0, '1',-1, '0'),DECODE (SIGN (c.range - q.distance),1, '1', 0, '1', -1, '0'), 0) = '1' ",//结业

							StringUtils.isNotBlank(order)?SqlUtis.orderBy(order):"");
			resultList = this.list(
					this.getStatementName("listByLngAndLat"), formMap, page);
		}
		jsonObject.put("goodsList", resultList);
		jsonObject.put("totals", page.getTotalCount());
		jsonObject.put("pages", page.getPages());
		return executeRerutn(jsonObject);
	}
	
	/**
	 * 商家商品列表查询
	 * 
	 * @param pageSize
	 *            页大小
	 * @param currentPage
	 *            当前页
	 * @param goodName
	 *            商品名字 
	 * @param shopId
	 *            商家id   
	 * @param goodType
	 *            商品类型                 
	 * @param barCode
	 *            条形码
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public JSONObject queryShopGoodsList(
			@Security
			String pageSize, 
			@Security
			String currentPage,
			@Security
			String goodType, 
			@Security
			String shopId, 
			@Security
			String goodName,
			@Security
			String barCode) {

		if (StringUtils.isBlank(pageSize)) {
			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
					"页大小不能为空");
		}
		if (StringUtils.isBlank(currentPage)) {
			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
					"当前页不能为空");
		}
		if(StringUtils.isBlank(shopId)){
			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
					"商家编码不能为空");
		}
		JSONObject jsonObject = new JSONObject();
		Page page = new Page();
		List resultList = new ArrayList();
		Map formMap = new HashMap();
		page.setPageSize(Integer.parseInt(pageSize));
		page.setPageNumber(Integer.parseInt(currentPage));
		String order = "case when a.recommended is null then 0 else to_number(a.recommended) end desc nulls last , case when a.seo_weight is null then 0 else a.seo_weight end desc nulls last , case when a.update_time is null then a.create_time else a.update_time end desc nulls last ";
		formMap.put(ErpMerchandiseType.COL_MERCHANDISE_TYPE_ID, goodType);
		formMap.put(ErpShop.COL_SHOP_ID, shopId);
		formMap.put(ErpMerchandise.COL_MERCHANDISE_NAME, goodName);
		formMap.put(ErpMerchandise.COL_BARCODE, barCode);
		formMap.put(ErpMerchandise.COL_CLOSED, Constants.GOODS_OPEN);//上架商品
		formMap.put(ErpMerchandise.COL_VIRTUAL, Constants.GOODS_UNVIRTUAL);//普通商品
		/*TODO 排序
		 * 0:默认(推荐优先，权重其次，再次更新时间)
		 * 1:销量(sales)高到低 
		 * 2：价格(price)高到低 
		 * 3：价格（price）低到高
		 * 4：距离(distance)近到远
		 * */
		
		SqlUtis.prepareSql(formMap, 
					StringUtils.isBlank(goodType)?"":"and a.shop_MERCHANDISE_TYPE_ID in (select MERCHANDISE_TYPE_ID from erp_merchandise_type where MERCHANDISE_TYPE_ID = '"+goodType+"' or parent_id='"+goodType+"')",
					SqlUtis.getSQL(formMap,"c." + ErpShop.COL_SHOP_ID, Types.VARCHAR,SqlUtis.EQ), 
					SqlUtis.getSQL(formMap, "a."+ErpMerchandise.COL_MERCHANDISE_NAME, Types.VARCHAR, SqlUtis.LIKE),
					SqlUtis.getSQL(formMap, "a."+ErpMerchandise.COL_BARCODE, Types.VARCHAR, SqlUtis.EQ),
					SqlUtis.getSQL(formMap, "a."+ErpMerchandise.COL_CLOSED, Types.VARCHAR, SqlUtis.EQ),
					SqlUtis.getSQL(formMap, "a."+ErpMerchandise.COL_VIRTUAL, Types.VARCHAR, SqlUtis.EQ),
					StringUtils.isNotBlank(order)?SqlUtis.orderBy(order):"" );
		resultList = this.list(formMap, page);
		jsonObject.put("goodsList", resultList);
		jsonObject.put("totals", page.getTotalCount());
		jsonObject.put("pages", page.getPages());
		return executeRerutn(jsonObject);
	}

	/**
	 * 商品详情
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public JSONObject querydetails(Map map){
		if (map.get("merchandiseId")==null || "".equals(map.get("merchandiseId"))) {
			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
					"商品代码不能为空");
		}
		JSONObject jsonObject = new JSONObject();
		Map formMap = new HashMap();
		formMap.put("merchandiseId", map.get("merchandiseId"));
		try {
			Map inventorymap = new HashMap();
			inventorymap.put(ErpInventory.COL_MERCHANDISE_ID, map.get("merchandiseId"));
			SqlUtis.prepareSql(inventorymap, SqlUtis.getSQL(inventorymap, ErpInventory.COL_MERCHANDISE_ID,Types.VARCHAR,SqlUtis.EQ)
					);
			boolean exists = inventoryService.exists(inventorymap); //查询是否有库存记录
			if(exists){
				//如果是登录用户，则增加浏览历史
				if(map.get("userId")!=null){ 
					ErpProductHis erpProductHis = new ErpProductHis();
					erpProductHis.setCreateName(!"".equals(map.get("username"))?"":map.get("username").toString());
					erpProductHis.setMerchandiseId(map.get("merchandiseId").toString());
					erpProductHis.setUserId(map.get("userId").toString());
					Map insertMapHis = new HashMap();
					insertMapHis.put("erpProductHis", erpProductHis);
					erpProductHisService.insert(insertMapHis);
				}
				//增加商品点击量
				Map erpstatmap = new HashMap();
				erpstatmap.put(ErpStat.COL_MERCHANDISE_ID, map.get("merchandiseId"));
				SqlUtis.prepareSql(erpstatmap, SqlUtis.getSQL(erpstatmap, ErpStat.COL_MERCHANDISE_ID,Types.VARCHAR,SqlUtis.EQ)
						);
				if(erpStatService.exists(erpstatmap)){
					ErpStat erpStat = erpStatService.one(erpstatmap);
					ErpStats UpErpStat = new ErpStats();
					UpErpStat.setHits(erpStat.getHits()+1);//点击量
					erpstatmap.put("erpStat", UpErpStat);
					try {
						erpStatService.update(erpstatmap);
					} catch (Exception e) {
						e.printStackTrace();
					}
				}else{
					ErpStats erpStat = new ErpStats();
					erpStat.setCreateName(!"".equals(map.get("username"))?"":map.get("username").toString());
					erpStat.setMerchandiseId(map.get("merchandiseId").toString());
					erpStat.setHits(1);
					Map insertMap = new HashMap();
					insertMap.put("erpStat", erpStat);
					erpStatService.insert(insertMap);
				}
				Goods goods = this.one(this.getStatementName("queryGoodsById"), formMap);
				
				//查询商品相关图片
				Map imageMap = new HashMap();
				imageMap.put(ErpMerchandiseImage.COL_GOODS_ID, map.get("merchandiseId"));
				SqlUtis.prepareSql(imageMap, SqlUtis.getSQL(imageMap, ErpMerchandiseImage.COL_GOODS_ID,Types.VARCHAR,SqlUtis.EQ),SqlUtis.orderBy(ErpMerchandiseImage.COL_SORT));
			    
				List<ErpMerchandiseImage> listImage = erpMerchandiseImageService.list(imageMap);
				List list = new ArrayList();
				if(listImage!=null){
					for (int i = 0; i < listImage.size(); i++) {
						Map imgsMap = new HashMap();
					    imgsMap.put("imgId", listImage.get(i).getImageId());
					    imgsMap.put("imgUrl", listImage.get(i).getImageUrl());
					    list.add(imgsMap);
					}
					
				}
				jsonObject.put("details",goods);
				jsonObject.put("images", list);
			}else{
				return executeRerutnFail(jsonObject);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return executeRerutn(jsonObject);
	}
}
